package com.olap.starter.common.util;

import com.olap.starter.common.util.ExceptionParse;
import com.olap.starter.util.servlet.ServletUtils;
import com.olap.starter.util.StringUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jxls.common.Context;
import org.jxls.transform.Transformer;
import org.jxls.util.JxlsHelper;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.util.ResourceUtils;
import org.springframework.web.multipart.MultipartFile;

import jakarta.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.nio.file.Files;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;


@Slf4j
public class ExcelUtil {

    private final static String TEMPLATE_PATH = "template" + File.separator;

    public static void exportExcel(HttpServletResponse response, Map<String, Object> model, String templateName) {
        exportExcel(response, model, templateName, dateFmt(new Date(), "yyyyMMddHHmmss") + ".xls");
    }

    public static void exportExcel(Map<String, Object> model, String templateName) {
        exportExcel(ServletUtils.getResponse(), model, templateName, dateFmt(new Date(), "yyyyMMddHHmmss") + ".xls");
    }

    public static void exportExcel(HttpServletResponse response, Map<String, Object> model, String templateName, String fileName) {
        try {
            response.setHeader("Content-dispostion", String.format("attachment;filename=\"%s\"", URLEncoder.encode(fileName, "UTF-8")));
            response.setContentType("application/octet-stream;charset=UTF-8");
            OutputStream os = response.getOutputStream();
            InputStream templateInputStream = getTemplateInputStream(templateName);
            if (null != templateInputStream) {
                exportExcel(templateInputStream, os, model);
            }
        } catch (IOException e) {
            log.error(ExceptionParse.getExceptionAllInfo(e));
            throw new RuntimeException(e);
        }
    }

    public static void exportExcel(InputStream is, OutputStream os, Map<String, Object> model) throws IOException {
        Context context = new Context();
        if (model != null) {
            for (String key : model.keySet()) {
                context.putVar(key, model.get(key));
            }
        }
        JxlsHelper jxlsHelper = JxlsHelper.getInstance();
        Transformer transformer = jxlsHelper.createTransformer(is, os);
        //JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator) transformer.getTransformationConfig().getExpressionEvaluator();
        //Map<String, Object> funcs = new HashMap<String, Object>();
        //  funcs.put("utils", new JxlsUtils());    //添加自定义功能
        //  evaluator.getJexlEngine().setFunctions(funcs);
        jxlsHelper.processTemplate(context, transformer);
    }

    public static void exportExcel(File xls, File out, Map<String, Object> model) throws FileNotFoundException, IOException {
        exportExcel(Files.newInputStream(xls.toPath()), Files.newOutputStream(out.toPath()), model);
    }

    public static void exportExcel(String templateName, OutputStream os, Map<String, Object> model) throws FileNotFoundException, IOException {
        File template = getTemplate(templateName);
        if (template != null) {
            exportExcel(Files.newInputStream(template.toPath()), os, model);
        }
    }

    /**
     * 获取jxls模版文件
     *
     * @param name
     * @return
     */
    public static InputStream getTemplateInputStream(String name) throws IOException {
        System.out.println("classpath:" + TEMPLATE_PATH + name);
        Resource resource = new ClassPathResource(TEMPLATE_PATH + name);
        return resource.getInputStream();
    }

    /**
     * 获取jxls模版文件
     *
     * @param name
     * @return
     */
    public static File getTemplate(String name) throws FileNotFoundException {
        File template = null;
        try {
            template = ResourceUtils.getFile("classpath:" + TEMPLATE_PATH + name);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        if (null != template && template.exists()) {
            return template;
        }
        return null;
    }

    /**
     * 日期格式化
     *
     * @param date
     * @param fmt
     * @return
     */
    public static String dateFmt(Date date, String fmt) {
        if (date == null) {
            return "";
        }
        try {
            SimpleDateFormat dateFmt = new SimpleDateFormat(fmt);
            return dateFmt.format(date);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "";
    }

    public static List<String> getTitle(Sheet sheet) {
        List<String> title = new ArrayList<>();
        Row row = sheet.getRow(0);
        for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
            title.add(getCellValue(row.getCell(i)));
        }
        return title;
    }

    public static List<Map<String, Object>> WorkbookToListMap(Workbook wb) {
        List<Map<String, Object>> data = new ArrayList<>();
        if (wb != null) {
            Sheet sheet = wb.getSheetAt(0);// 只读第一个sheet
            int rowNum = sheet.getPhysicalNumberOfRows();// 获取最大行数
            List<String> title = getTitle(sheet);

            Row row;
            for (int i = 1; i < rowNum; i++) {// 第一行是抬头, 从第二行开始读
                row = sheet.getRow(i);
                if (row != null) {
                    int colNum = row.getLastCellNum();// 获取最大列数
                    Map<String, Object> map = new HashMap<>();
                    for (int j = 0; j < colNum; j++) {
                        String cellValue = getCellValue(row.getCell(j));
                        if (StringUtils.isNotBlank(cellValue)) {
                            map.put(title.get(j), cellValue);
                        }
                    }
                    if (map.size() != 0) {
                        data.add(map);
                    }
                } else if (i == 1) {
                    throw new RuntimeException("上传文件有误, 请检查后重试");
                }
            }
            try {
                wb.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        } else {
            throw new RuntimeException("上传文件有误, 请检查后重试");
        }
        return data;
    }

    public static void main(String[] args) throws ParseException, IOException {
        Workbook wb = importExcel("F:\\11test\\1.xlsx");

      /* OutputStream os = Files.newOutputStream(Paths.get("D:/test.xlsx"));
        Map<String, Object> models = generateModel();
        String json = "{\"date\":{\"datetime\":\"2023-01-06\",\"month\":\"12\",\"year\":\"2022\"},\"data\":[{\"PROD_SHIFT_NO_2_WT\":6573.768,\"PROD_SHIFT_NO_3_WT\":5100.274,\"PROD_SHIFT_NO_3_NUM\":24,\"UNIT_CODE\":\"A\",\"PROD_SHIFT_NO_1_WT\":375.956,\"PROD_SHIFT_GROUP_B_NUM\":8,\"PROD_SHIFT_GROUP_B_WT\":895.97,\"PROD_SHIFT_GROUP_C_WT\":7001.18,\"PROD_SHIFT_GROUP_D_NUM\":24,\"PROD_SHIFT_GROUP_D_WT\":2096.988,\"PROD_SHIFT_NO_2_NUM\":62,\"PROD_SHIFT_GROUP_A_WT\":2055.86,\"YEAR_NUM\":102,\"MONTH_NUM\":4,\"FACTORY_DIV\":\"1\",\"PROD_SHIFT_NO_1_NUM\":4,\"YEAR_WT\":23719.458,\"PROD_SHIFT_GROUP_A_NUM\":16,\"MONTH_WT\":89.25,\"PROD_SHIFT_GROUP_C_NUM\":42},{\"PROD_SHIFT_NO_2_WT\":5675.45,\"PROD_SHIFT_NO_3_WT\":3434.7,\"PROD_SHIFT_NO_3_NUM\":30,\"UNIT_CODE\":\"G\",\"PROD_SHIFT_NO_1_WT\":993.012,\"PROD_SHIFT_GROUP_B_NUM\":24,\"PROD_SHIFT_GROUP_B_WT\":3166.834,\"PROD_SHIFT_GROUP_C_WT\":1034.85,\"PROD_SHIFT_GROUP_D_NUM\":24,\"PROD_SHIFT_GROUP_D_WT\":3376.75,\"PROD_SHIFT_NO_2_NUM\":40,\"PROD_SHIFT_GROUP_A_WT\":2524.728,\"YEAR_NUM\":80,\"MONTH_NUM\":0,\"FACTORY_DIV\":\"3\",\"PROD_SHIFT_NO_1_NUM\":10,\"YEAR_WT\":10103.162,\"PROD_SHIFT_GROUP_A_NUM\":24,\"MONTH_WT\":0,\"PROD_SHIFT_GROUP_C_NUM\":8},{\"PROD_SHIFT_NO_2_WT\":883.332,\"PROD_SHIFT_NO_3_WT\":204.886,\"PROD_SHIFT_NO_3_NUM\":4,\"UNIT_CODE\":\"7\",\"PROD_SHIFT_NO_1_WT\":354.582,\"PROD_SHIFT_GROUP_B_NUM\":6,\"PROD_SHIFT_GROUP_B_WT\":288.686,\"PROD_SHIFT_GROUP_C_WT\":949.228,\"PROD_SHIFT_GROUP_D_NUM\":2,\"PROD_SHIFT_GROUP_D_WT\":86.222,\"PROD_SHIFT_NO_2_NUM\":18,\"PROD_SHIFT_GROUP_A_WT\":118.664,\"YEAR_NUM\":28,\"MONTH_NUM\":0,\"FACTORY_DIV\":\"3\",\"PROD_SHIFT_NO_1_NUM\":4,\"YEAR_WT\":1459.736,\"PROD_SHIFT_GROUP_A_NUM\":2,\"MONTH_WT\":0,\"PROD_SHIFT_GROUP_C_NUM\":16},{\"PROD_SHIFT_NO_2_WT\":29.58,\"PROD_SHIFT_NO_3_WT\":411.4,\"PROD_SHIFT_NO_3_NUM\":8,\"UNIT_CODE\":\"3\",\"PROD_SHIFT_NO_1_WT\":88.88,\"PROD_SHIFT_GROUP_B_NUM\":4,\"PROD_SHIFT_GROUP_B_WT\":59.5,\"PROD_SHIFT_GROUP_C_WT\":0,\"PROD_SHIFT_GROUP_D_NUM\":4,\"PROD_SHIFT_GROUP_D_WT\":59.12,\"PROD_SHIFT_NO_2_NUM\":2,\"PROD_SHIFT_GROUP_A_WT\":411.24,\"YEAR_NUM\":16,\"MONTH_NUM\":0,\"FACTORY_DIV\":\"1\",\"PROD_SHIFT_NO_1_NUM\":6,\"YEAR_WT\":529.86,\"PROD_SHIFT_GROUP_A_NUM\":8,\"MONTH_WT\":0,\"PROD_SHIFT_GROUP_C_NUM\":0},{\"PROD_SHIFT_NO_2_WT\":1651.238,\"PROD_SHIFT_NO_3_WT\":2098.334,\"PROD_SHIFT_NO_3_NUM\":30,\"UNIT_CODE\":\"E\",\"PROD_SHIFT_NO_1_WT\":2071.316,\"PROD_SHIFT_GROUP_B_NUM\":46,\"PROD_SHIFT_GROUP_B_WT\":3392.186,\"PROD_SHIFT_GROUP_C_WT\":1999.962,\"PROD_SHIFT_GROUP_D_NUM\":8,\"PROD_SHIFT_GROUP_D_WT\":175.036,\"PROD_SHIFT_NO_2_NUM\":30,\"PROD_SHIFT_GROUP_A_WT\":253.704,\"YEAR_NUM\":96,\"MONTH_NUM\":0,\"FACTORY_DIV\":\"2\",\"PROD_SHIFT_NO_1_NUM\":36,\"YEAR_WT\":5820.888,\"PROD_SHIFT_GROUP_A_NUM\":4,\"MONTH_WT\":0,\"PROD_SHIFT_GROUP_C_NUM\":38},{\"PROD_SHIFT_NO_2_WT\":254.25,\"PROD_SHIFT_NO_3_WT\":1097.82,\"PROD_SHIFT_NO_3_NUM\":14,\"UNIT_CODE\":\"2\",\"PROD_SHIFT_NO_1_WT\":63.518,\"PROD_SHIFT_GROUP_B_NUM\":6,\"PROD_SHIFT_GROUP_B_WT\":669.566,\"PROD_SHIFT_GROUP_C_WT\":275.456,\"PROD_SHIFT_GROUP_D_NUM\":8,\"PROD_SHIFT_GROUP_D_WT\":449.356,\"PROD_SHIFT_NO_2_NUM\":8,\"PROD_SHIFT_GROUP_A_WT\":21.21,\"YEAR_NUM\":26,\"MONTH_NUM\":0,\"FACTORY_DIV\":\"1\",\"PROD_SHIFT_NO_1_NUM\":4,\"YEAR_WT\":1415.588,\"PROD_SHIFT_GROUP_A_NUM\":6,\"MONTH_WT\":0,\"PROD_SHIFT_GROUP_C_NUM\":6},{\"PROD_SHIFT_NO_2_WT\":3898.076,\"PROD_SHIFT_NO_3_WT\":966.508,\"PROD_SHIFT_NO_3_NUM\":24,\"UNIT_CODE\":\"B\",\"PROD_SHIFT_NO_1_WT\":470.986,\"PROD_SHIFT_GROUP_B_NUM\":20,\"PROD_SHIFT_GROUP_B_WT\":1242.338,\"PROD_SHIFT_GROUP_C_WT\":1163.7,\"PROD_SHIFT_GROUP_D_NUM\":30,\"PROD_SHIFT_GROUP_D_WT\":1674.39,\"PROD_SHIFT_NO_2_NUM\":38,\"PROD_SHIFT_GROUP_A_WT\":1255.142,\"YEAR_NUM\":76,\"MONTH_NUM\":0,\"FACTORY_DIV\":\"1\",\"PROD_SHIFT_NO_1_NUM\":14,\"YEAR_WT\":5335.57,\"PROD_SHIFT_GROUP_A_NUM\":12,\"MONTH_WT\":0,\"PROD_SHIFT_GROUP_C_NUM\":14},{\"PROD_SHIFT_NO_2_WT\":1946.804,\"PROD_SHIFT_NO_3_WT\":1377.166,\"PROD_SHIFT_NO_3_NUM\":26,\"UNIT_CODE\":\"1\",\"PROD_SHIFT_NO_1_WT\":8.484,\"PROD_SHIFT_GROUP_B_NUM\":40,\"PROD_SHIFT_GROUP_B_WT\":957.916,\"PROD_SHIFT_GROUP_C_WT\":128.676,\"PROD_SHIFT_GROUP_D_NUM\":4,\"PROD_SHIFT_GROUP_D_WT\":627.276,\"PROD_SHIFT_NO_2_NUM\":32,\"PROD_SHIFT_GROUP_A_WT\":1618.586,\"YEAR_NUM\":60,\"MONTH_NUM\":0,\"FACTORY_DIV\":\"1\",\"PROD_SHIFT_NO_1_NUM\":2,\"YEAR_WT\":3332.454,\"PROD_SHIFT_GROUP_A_NUM\":12,\"MONTH_WT\":0,\"PROD_SHIFT_GROUP_C_NUM\":4},{\"PROD_SHIFT_NO_2_WT\":123.268,\"PROD_SHIFT_NO_3_WT\":67.914,\"PROD_SHIFT_NO_3_NUM\":8,\"UNIT_CODE\":\"5\",\"PROD_SHIFT_NO_1_WT\":136.312,\"PROD_SHIFT_GROUP_B_NUM\":2,\"PROD_SHIFT_GROUP_B_WT\":4.234,\"PROD_SHIFT_GROUP_C_WT\":63.894,\"PROD_SHIFT_GROUP_D_NUM\":14,\"PROD_SHIFT_GROUP_D_WT\":131.898,\"PROD_SHIFT_NO_2_NUM\":10,\"PROD_SHIFT_GROUP_A_WT\":127.468,\"YEAR_NUM\":32,\"MONTH_NUM\":0,\"FACTORY_DIV\":\"2\",\"PROD_SHIFT_NO_1_NUM\":14,\"YEAR_WT\":327.494,\"PROD_SHIFT_GROUP_A_NUM\":10,\"MONTH_WT\":0,\"PROD_SHIFT_GROUP_C_NUM\":6},{\"PROD_SHIFT_NO_2_WT\":685.4,\"PROD_SHIFT_NO_3_WT\":352.632,\"PROD_SHIFT_NO_3_NUM\":4,\"UNIT_CODE\":\"4\",\"PROD_SHIFT_NO_1_WT\":0,\"PROD_SHIFT_GROUP_B_NUM\":0,\"PROD_SHIFT_GROUP_B_WT\":0,\"PROD_SHIFT_GROUP_C_WT\":685.4,\"PROD_SHIFT_GROUP_D_NUM\":0,\"PROD_SHIFT_GROUP_D_WT\":0,\"PROD_SHIFT_NO_2_NUM\":10,\"PROD_SHIFT_GROUP_A_WT\":352.632,\"YEAR_NUM\":14,\"MONTH_NUM\":0,\"FACTORY_DIV\":\"2\",\"PROD_SHIFT_NO_1_NUM\":0,\"YEAR_WT\":1038.032,\"PROD_SHIFT_GROUP_A_NUM\":4,\"MONTH_WT\":0,\"PROD_SHIFT_GROUP_C_NUM\":10},{\"PROD_SHIFT_NO_2_WT\":3582.548,\"PROD_SHIFT_NO_3_WT\":3124.88,\"PROD_SHIFT_NO_3_NUM\":40,\"UNIT_CODE\":\"D\",\"PROD_SHIFT_NO_1_WT\":1364.252,\"PROD_SHIFT_GROUP_B_NUM\":36,\"PROD_SHIFT_GROUP_B_WT\":2808.482,\"PROD_SHIFT_GROUP_C_WT\":2976.292,\"PROD_SHIFT_GROUP_D_NUM\":8,\"PROD_SHIFT_GROUP_D_WT\":583.246,\"PROD_SHIFT_NO_2_NUM\":38,\"PROD_SHIFT_GROUP_A_WT\":1703.66,\"YEAR_NUM\":104,\"MONTH_NUM\":0,\"FACTORY_DIV\":\"2\",\"PROD_SHIFT_NO_1_NUM\":26,\"YEAR_WT\":8071.68,\"PROD_SHIFT_GROUP_A_NUM\":22,\"MONTH_WT\":0,\"PROD_SHIFT_GROUP_C_NUM\":38},{\"PROD_SHIFT_NO_2_WT\":852.46,\"PROD_SHIFT_NO_3_WT\":594.11,\"PROD_SHIFT_NO_3_NUM\":16,\"UNIT_CODE\":\"F\",\"PROD_SHIFT_NO_1_WT\":285.5,\"PROD_SHIFT_GROUP_B_NUM\":8,\"PROD_SHIFT_GROUP_B_WT\":238.818,\"PROD_SHIFT_GROUP_C_WT\":360.886,\"PROD_SHIFT_GROUP_D_NUM\":12,\"PROD_SHIFT_GROUP_D_WT\":645.912,\"PROD_SHIFT_NO_2_NUM\":18,\"PROD_SHIFT_GROUP_A_WT\":486.454,\"YEAR_NUM\":42,\"MONTH_NUM\":0,\"FACTORY_DIV\":\"2\",\"PROD_SHIFT_NO_1_NUM\":8,\"YEAR_WT\":1732.07,\"PROD_SHIFT_GROUP_A_NUM\":12,\"MONTH_WT\":0,\"PROD_SHIFT_GROUP_C_NUM\":10}]}\n";
        Map<String, Object> model = JSONObject.parseObject(json);

        exportExcel("DDRCL.xlsx", os, model);
        os.close();*/
    }

    /**
     * 导入Excel
     *
     * @param filePath excel文件路径
     * @return Workbook
     */
    public static Workbook importExcel(String filePath) {
        if (StringUtils.isBlank(filePath) || "null".equals(filePath)) {
            return null;
        }
        String suf = filePath.substring(filePath.lastIndexOf("."));
        InputStream is = null;
        try {
            is = new FileInputStream(filePath);
            if (".xls".equals(suf)) {
                return new HSSFWorkbook(is);
            } else if (".xlsx".equals(suf)) {
                return new XSSFWorkbook(is);
            } else {
                return null;
            }
        } catch (IOException e) {
            log.error(">>> Excel表格读取错误", e);
            return null;
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 导入Excel
     *
     * @return Workbook
     */

    public static File transferToFile(MultipartFile multipartFile) {
//        选择用缓冲区来实现这个转换即使用java 创建的临时文件 使用 MultipartFile.transferto()方法 。
        File file = null;
        try {
            String originalFilename = multipartFile.getOriginalFilename();
            String[] filename = originalFilename.split("\\.");
            file = File.createTempFile("temp", filename.length > 1 ? filename[1] : filename[0] + ".");
            multipartFile.transferTo(file);
            file.deleteOnExit();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return file;
    }

    public static Workbook importExcel(MultipartFile file) {
        if (file == null || file.isEmpty()) {
            return null;
        }
        String fileName = file.getOriginalFilename();
        String suf = fileName.substring(fileName.lastIndexOf("."));
        InputStream is = null;
        try {
            is = new FileInputStream(transferToFile(file));
            if (".xls".equals(suf)) {
                return new HSSFWorkbook(is);
            } else if (".xlsx".equals(suf)) {
                return new XSSFWorkbook(is);
            } else {
                return null;
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return null;
    }

    /**
     * 读取表格数据
     *
     * @param cell 一格
     * @return String
     */
    public static String getCellValue(Cell cell) {
        if (cell == null) {
            return null;
        }
        String cellValue;
        CellType cellType = cell.getCellTypeEnum();
        switch (cellType) {
            case NUMERIC:// 数字类型
                boolean cellDateFormatted = HSSFDateUtil.isCellDateFormatted(cell);
                if (cellDateFormatted) {// 处理日期格式、时间格式
                    SimpleDateFormat sdf;
                    short dataFormat = cell.getCellStyle().getDataFormat();
                    if (dataFormat == 14 || dataFormat == 179 || dataFormat == 177) {
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                    } else if (dataFormat == 21) {
                        sdf = new SimpleDateFormat("HH:mm:ss");
                    } else if (dataFormat == 22) {
                        sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    } else {
                        try {
                            sdf = new SimpleDateFormat("yyyy-MM-dd");
                        } catch (Exception e) {
                            throw new RuntimeException(">>> 日期格式错误");
                        }
                    }
                    Date date = cell.getDateCellValue();
                    cellValue = sdf.format(date);
                } else {// 处理数值格式
                    double numericCellValue = cell.getNumericCellValue();
                    DecimalFormat df = new DecimalFormat("#.######");
                    cellValue = df.format(numericCellValue);
                }
                break;
            case STRING:// 字符串类型
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case BOOLEAN:// 布尔类型
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA:// 公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case BLANK:// 空值
                cellValue = null;
                break;
            case _NONE:// 空值
                cellValue = null;
                break;
            case ERROR:// 异常
                cellValue = "#非法字符#";
                break;
            default:
                cellValue = "#未知类型#";
                break;
        }
        return cellValue;
    }
}
